/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package models.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import models.DBHandler;
import models.beans.CoursesBean;
import models.beans.CoursesUserBean;

/**
 *
 * @author Group 3 Aprotrain Aptech
 */
public class CousesUserDAO {
    public static int addNewCousesUser(CoursesUserBean coursesUserBean) throws ClassNotFoundException, SQLException {
        int result = 0;
        ResultSet rs = null;
        Connection conn = DBHandler.getConnection();
        PreparedStatement pstm = conn.prepareStatement("SELECT * FROM [Mail_Server].[dbo].[tblCoursesUser] WHERE [userId]=? AND [coursesId]= ?");
        pstm.setString(1, coursesUserBean.getUserId());
        pstm.setInt(2, coursesUserBean.getCoursesId());

        rs = pstm.executeQuery();
        if(rs.next()){
            return result = 0;
        }
        else{
            pstm = conn.prepareStatement("INSERT INTO [Mail_Server].[dbo].[tblCoursesUser]([userId],[coursesId]) VALUES(?,?)");
            pstm.setString(1, coursesUserBean.getUserId());
            pstm.setInt(2, coursesUserBean.getCoursesId());
            result = pstm.executeUpdate();
            DBHandler.closeDBObj(pstm);
            DBHandler.closeDBObj(conn);
            return result;
        }        
        
    }
public static List<CoursesUserBean> getStudentByCourses(int coursesId) throws ClassNotFoundException, SQLException {
        List<CoursesUserBean> list = new ArrayList<CoursesUserBean>();
        ResultSet rs = null;
        Connection conn = DBHandler.getConnection();
        PreparedStatement pstm = conn.prepareStatement("SELECT dbo.tblUser.userName, dbo.tblCourses.coursesName,dbo.tblCoursesUser.coursesId, dbo.tblCoursesUser.userId, dbo.tblUser.class FROM dbo.tblCoursesUser INNER JOIN dbo.tblCourses ON dbo.tblCoursesUser.coursesId = dbo.tblCourses.coursesId INNER JOIN dbo.tblUser ON dbo.tblCoursesUser.userId = dbo.tblUser.userId WHERE (dbo.tblCoursesUser.coursesId = ?)");
        pstm.setInt(1, coursesId);
        rs = pstm.executeQuery();

        CoursesUserBean coursesUserBean = null;
        while (rs.next()) {
            coursesUserBean = new CoursesUserBean();
            coursesUserBean.setCoursesId(rs.getInt("coursesId"));
            coursesUserBean.setCoursesName(rs.getString("coursesName"));
            coursesUserBean.setUserName(rs.getString("userName"));
            coursesUserBean.setCoursesName(rs.getString("coursesName"));
            list.add(coursesUserBean);
        }
        DBHandler.closeDBObj(rs);
        DBHandler.closeDBObj(pstm);
        DBHandler.closeDBObj(conn);

        return list;
    }

    
    
}
